﻿using Camelot.JSONAPI.Models;
using Camelot.SharePointConnector.Data;
using Camelot.SharePointIntegration;
using Camelot.SharePointIntegration.Client;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.ServiceModel;
using System.Xml;

namespace Camelot.JSONAPI.Business
{
    public class ExecuteFactory
    {
        /// <summary>
        /// Execute commands
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public static string Execute(Command command)
        {

            // Fetch the connection string for the command
            string connectionString;
            command.Connection = string.IsNullOrEmpty(command.Connection) ? "Default" : command.Connection;

            if (ConfigurationManager.ConnectionStrings[command.Connection] == null)
            {
                throw new FaultException(string.Format("Could not get the ConnectionString [{0}], please check your web.config file", command.Connection));
            }
            else
            {
                connectionString = ConfigurationManager.ConnectionStrings[command.Connection].ConnectionString;
            }

            if (command == null)
            {
                throw new FaultException("You must specify the command parameters");
            }

            string query = string.Empty;

            // If SQL
            if (!string.IsNullOrEmpty(command.Sql))
            {
                query = command.Sql;
            }
            else
            {
                // This will automatically be a select
                if (string.IsNullOrEmpty(command.List))
                {
                    throw new FaultException("You must either specify a List name or a SQL query");
                }

                // Set command defaults
                var type = string.IsNullOrEmpty(command.Type) ? "SELECT" : command.Type;

                // SELECT
                if (type.Equals("SELECT", StringComparison.InvariantCultureIgnoreCase))
                {
                    string fields = command.Fields == null ? "*" : command.Fields.ToString();
                    string where = string.IsNullOrEmpty(command.Where) ? "" : string.Concat(" WHERE ", command.Where);
                    string limit = string.IsNullOrEmpty(command.Limit) ? "" : string.Concat(" LIMIT ", command.Limit);
                    string suffix = string.IsNullOrEmpty(command.Suffix) ? "" : string.Format(".`{0}`", command.Suffix);
                    string order = string.IsNullOrEmpty(command.Order) ? "" : string.Format(" ORDER BY {0}", command.Order);

                    query = string.Format("{0} {1} FROM `{2}`{3}{4}{5}{6}", type, fields, command.List, suffix, where, order, limit);
                }

                // SHOW
                if (type.ToUpper().Contains("SHOW"))
                {
                    if (type.Contains("TABLES"))
                    {
                        query = "SHOW TABLES";
                    }
                    else
                    {
                        string suffix = string.IsNullOrEmpty(command.Suffix) ? "" : string.Format(".`{0}`", command.Suffix);
                        query = string.Format("{0} FROM `{1}`{2}", type, command.List, suffix);
                    }
                }

                // INSERT
                if (type.Equals("INSERT", StringComparison.InvariantCultureIgnoreCase))
                {
                    // Check the fields
                    if (command.Fields == null)
                    {
                        throw new FaultException("UPDATE requires the Field parameter to be set");
                    }

                    // Convert the command.Fields to something usable
                    Dictionary<string, string> updateFields = ((JArray)command.Fields).ToDictionary(field => (field).Cast<KeyValuePair<string, JToken>>().First().Key, field => (field).Cast<KeyValuePair<string, JToken>>().First().Value.ToString());

                    string fields = string.Empty;
                    string vaules = string.Empty;

                    foreach (var updateField in updateFields)
                    {
                        if (updateField.Equals(updateFields.Last()))
                        {
                            fields += string.Format("`{0}`", updateField.Key);
                            vaules += string.Format("'{0}'", updateField.Value);
                        }
                        else
                        {
                            fields += string.Format("`{0}`,", updateField.Key);
                            vaules += string.Format("'{0}',", updateField.Value);
                        }
                    }

                    query = string.Format("INSERT INTO `{0}` ({1}) VALUES ({2})", command.List, fields, vaules);
                }

                // UPDATE
                if (type.Equals("UPDATE", StringComparison.InvariantCultureIgnoreCase))
                {
                    // Check that either Id or Where is stated
                    if (string.IsNullOrEmpty(command.Id) && string.IsNullOrEmpty(command.Where))
                    {
                        throw new FaultException("UPDATE requires either Id or Where parameters to be set");
                    }

                    // Check the fields
                    if (command.Fields == null)
                    {
                        throw new FaultException("UPDATE requires the Field parameter to be set");
                    }

                    // Convert the command.Fields to something usable
                    Dictionary<string, string> updateFields = ((JArray)command.Fields).ToDictionary(field => (field).Cast<KeyValuePair<string, JToken>>().First().Key, field => (field).Cast<KeyValuePair<string, JToken>>().First().Value.ToString());

                    //string set = updateFields.Aggregate(string.Empty, (current, updateField) => current + string.Format("{0} = '{1}'", updateField.Key, updateField.Value));

                    string set = string.Empty;
                    foreach (var updateField in updateFields)
                    {
                        if (updateField.Equals(updateFields.Last()))
                        {
                            set += string.Format("`{0}` = '{1}'", updateField.Key, updateField.Value);
                        }
                        else
                        {
                            set += string.Format("`{0}` = '{1}', ", updateField.Key, updateField.Value);
                        }
                    }

                    var where = string.IsNullOrEmpty(command.Where) ? string.Format("ID = {0}", command.Id) : command.Where;

                    query = string.Format("UPDATE `{0}` SET {1} WHERE {2}", command.List, set, where);
                }

                // DELETE
                if (type.Equals("DELETE", StringComparison.InvariantCultureIgnoreCase))
                {
                    // Check that either Id or Where is stated
                    if (string.IsNullOrEmpty(command.Id) && string.IsNullOrEmpty(command.Where))
                    {
                        throw new FaultException("To use DELETE you must specify either Id or Where");
                    }

                    var where = string.IsNullOrEmpty(command.Where) ? string.Format("ID = {0}", command.Id) : command.Where;
                    query = string.Format("DELETE FROM `{0}` WHERE {1}", command.List, where);
                }

            }

            using (var connection = new SharePointConnection(connectionString))
            {
                connection.Open();

                // Switch database
                if (!string.IsNullOrEmpty(command.SitePath))
                {
                    using (var com = new SharePointCommand(string.Format("USE `{0}`", command.SitePath), connection))
                    {
                        com.ExecuteNonQuery();
                    }
                }

                // Command execution
                switch (query.QueryType())
                {
                    case Models.CommandType.SELECT:
                    case Models.CommandType.SHOW:
                        var listData = ListDataUtility.CreateListData(query, connection);
                        var camelotXml = CamelotXml.CreateXml(listData);

                        XmlDocument doc = new XmlDocument();
                        doc.LoadXml(camelotXml);

                        return JsonConvert.SerializeXmlNode(doc);
                        break;

                    case Models.CommandType.INSERT:
                    case Models.CommandType.UPDATE:
                    case Models.CommandType.DELETE:
                        try
                        {
                            using (var com = new SharePointCommand(query, connection))
                            {
                                com.ExecuteNonQuery();
                                return string.Format("{{ Result: 'Query successfully executed', ID: '{0}' }}", com.LastRecordsAffected);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new FaultException(string.Concat("Error: ", ex.Message));
                        }
                        break;
                    case Models.CommandType.CALL:
                        throw new FaultException("Binary operations is not supported");
                        break;

                }
            }

            return "{ Result: 'Method did not succeed' }";
        }
    }
}
